import os
import re
import sys
import pandas as pd
import numpy as np


# 总数据的汇总
def get_all_info(path):
    rgx = "(.*?)运动训练.xls"
    # 文件的获取
    full_info = pd.DataFrame(columns={"name", "user_id", "pro_name"})
    for i in os.listdir(path):
        filename = re.match(rgx, i)
        if filename:
            data_path = os.path.join(path, filename.group(0))
            part_data = get_not_normal_data(data_path)
            full_info = pd.concat([full_info, part_data], ignore_index=True, axis=0)
        else:
            data_path = os.path.join(path, i)
            part_data = get_normal_data(data_path)
            full_info = pd.concat([full_info, part_data], ignore_index=True, axis=0)
    # full_info.reset_index(inplace=True)

    return full_info


"""
get_normal_data
get_not_normal_data：运动训练的表
该年总的录取名单
"""


def get_normal_data(path):
    data_path = pd.read_excel(path)
    try:
        data_path.rename(columns={'姓名': "name", '身份证号': "user_id", '录取专业': "pro_name"}, inplace=True)
        part_data = data_path.loc[:, ["name", "user_id", "pro_name"]]
        part_data['user_id'] = part_data['user_id']
    except:
        data_path.rename(columns={'姓名': "name", '身份证号': "user_id", '录取（系科）专业': "pro_name"}, inplace=True)
        part_data = data_path.loc[:, ["name", "user_id", "pro_name"]]
    part_data.dropna(axis=0, inplace=True)
    return part_data


def get_not_normal_data(path):
    # 读取excel中的所有表
    sheet_names = list(pd.read_excel(path, sheet_name=None))
    data = pd.DataFrame(columns={"name", "user_id", "pro_name"})
    for i in sheet_names:
        data_sheet = pd.read_excel(path, sheet_name=i, skiprows=[0, 1], header=0, index_col=0)
        # 异常数理
        try:
            data_sheet.rename(columns={'身份证号': "user_id", '姓名': "name", '专业': "pro_name"}, inplace=True)
            data_sheet['user_id'] = data_sheet['user_id']
            data_sheet.dropna(axis=0, inplace=True)
        except:
            data_sheet.rename(columns={'姓名': "name", '专业': "pro_name"}, inplace=True)
            data_sheet["user_id"] = np.nan

        data_part_sheet = data_sheet.loc[:, ['user_id', 'name', 'pro_name']]

        data_part_sheet.dropna(axis=0, inplace=True)
        # 将表中的数据进行拼接
        data = pd.concat([data, data_part_sheet], ignore_index=True, axis=0)
    return data


# 检查数据的获取
def check_data(path):
    # 获取待检测的数据
    try:
        data = pd.read_excel(path, converters={"身份证号": str})
    except:
        data = pd.read_excel(path)
    if "录取（系科）专业" in data.columns or "录取专业名称" in data.columns:
        # 将空值进行删除
        data.dropna(axis=1, inplace=True)
        if "出生日期" in data.columns:
            data.drop(columns=['出生日期'], axis=1, inplace=True)
            data.rename(columns={"姓名": "name"}, inplace=True)
            data['user_id'] = np.NaN
        else:
            data.rename(columns={"姓名": "name", "身份证号": "user_id"}, inplace=True)
        if '录取（系科）专业' in data.columns:
            data.rename(columns={'录取（系科）专业': "pro_name"}, inplace=True)
        else:
            data.rename(columns={'录取专业名称': "pro_name"}, inplace=True)
    else:
        # 判断第一行是专业的情况
        if "姓名" in data.columns:
            for col in data.columns.tolist():
                if data[col].isnull().all():
                    data.drop(columns=col, axis=1, inplace=True)
            # 判断是否存在身份证号这一列
            if "身份证号" in data.columns:
                data.rename(columns={"姓名": "name", "身份证号": "user_id"}, inplace=True)

            else:
                data.rename(columns={"姓名": "name"}, inplace=True)
                data['user_id'] = np.NaN
            # 获取数据全部为NAN的index，并进行删除
            data.drop(index=data[pd.isna(data['name']) == True].index.tolist(), axis=0, inplace=True)
            # 获取最后一列的专业并进行删除
            pro = data.iloc[-1, 0]
            data.drop(index=data.tail(1).index.tolist(), axis=0, inplace=True)
            # 将专业进行设置
            data['pro_name'] = pro[5:]
        else:
            pro_name = data.columns[0][5:]
            # 重读data
            try:
                data = pd.read_excel(path, skiprows=[0, 1], header=0, converters={"身份证号": str})
            except:
                data = pd.read_excel(path)
            # 去除空列
            data.dropna(axis=1, inplace=True)
            if "出生日期" in data.columns:
                data.drop(columns=['出生日期'], axis=1, inplace=True)
                data.rename(columns={"姓名": "name"}, inplace=True)
                data['user_id'] = np.NaN
            else:
                data.rename(columns={"姓名": "name", "身份证号": "user_id"}, inplace=True)
            data['pro_name'] = pro_name
    return data


# 进行检查并输出错误
def checkout_error(full_data, check_data):
    # 根据身份证号获取
    # 首先确定user_id不是为空值的
    flag = check_data['user_id'].isnull().any()
    full_data.index = full_data.index.astype(int)
    check_data.index = check_data.index.astype(int)
    # 身份证号是否存在
    if flag == False:
        # 获取user id
        user_id_list = check_data['user_id'].tolist()
        # 提取出full_dataa中的相关数据并将对应位置上的数据删除
        full_data_part = full_data[full_data['user_id'].isin(user_id_list)].copy()
        full_data.drop(index=full_data.loc[full_data['user_id'].isin(user_id_list), :].index.tolist(), axis=0,
                       inplace=True)
        # 进行检查
        # 合并去重
        full_data_part['flag'] = '总表数据'

        check_data['flag'] = '检测表内的数据'

        check_part_data = pd.concat([check_data, full_data_part], axis=0, ignore_index=True)

        check_part_data.drop_duplicates(subset=['name', 'pro_name', 'user_id'], keep=False, inplace=True)
    else:
        # 通过姓名来获取数据
        names_list = check_data['name'].tolist()
        pro_name = check_data['pro_name'].tolist()
        # 获取总表中有names_list的所有数据
        full_part_data_name = full_data[full_data['name'].isin(names_list)].copy()
        full_part_data_name['flag'] = '总表数据'
        check_data['flag'] = '检测表内的数据'
        # 根据专业再次进行筛选
        # full_part_data_name_pro=full_part_data_name[full_part_data_name['pro_name'].isin(pro_name)]
        # 进行拼接
        check_part_data = pd.concat([check_data, full_part_data_name], axis=0, ignore_index=True)
        check_part_data.drop_duplicates(subset=['name', 'pro_name'], keep=False,
                                        inplace=True)  # -----------学生没有身份证，将身份证进行填充
    return check_part_data


# 错误信息的保存
def save_error_data(path, data):
    data = data.sort_values(by=['name', 'pro_name'])
    data.rename(columns={"name": "姓名", "user_id": "身份证", "pro_name": "专业"}, inplace=True)
    data.to_excel(path, index=None)


if __name__ == "__main__":
    full_data = get_all_info(r"H:\infomation\2012录取电子版")
    orc_data = check_data(r"H:\infomation\2012（PDF）\2012-JX13-0005\2012-JX13-0005-002.xlsx")
    check_error = checkout_error(full_data, orc_data)
    save_error_data("./error.xlsx", check_error)
